package gov.nhso.prov.acc.utils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.core.ConfigBundle;

import com.isud.core.ISUD;

@SuppressWarnings("rawtypes")
public class DataUtil {
	private ISUD dao = null;
	private String schema = ConfigBundle.getValue("SCHEMA");
	
	public DataUtil(ISUD dao) {
		this.dao = dao;
	}
	
	public List lsRole() throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT ROLE_ID, ROLE_NAME FROM " + this.schema + ".ACC67_ROLE WHERE ACTIVE = 'Y'");
		return lsData;
	}
	public List lsRole(String param) throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT ROLE_ID, ROLE_NAME FROM " + this.schema + ".ACC67_ROLE WHERE ACTIVE = 'Y' AND ROLE_ID IN ("+ param +")");
		return lsData;
	}
	public List lsNhsoZone() throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT DISTINCT NHSO_ZONE, NHSO_ZONENAME FROM " + this.schema + ".PROVINCE WHERE NHSO_ZONE NOT IN ('14', '15') ORDER BY NHSO_ZONE ASC");
		return lsData;
	}
	
	public String getNhsoZoneName(String nhsoZone) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT NHSO_ZONENAME FROM " + this.schema + ".PROVINCE WHERE NHSO_ZONE = '" + nhsoZone + "'").get("NHSO_ZONENAME"));
		return result;
	}
	
	public String getProvinceName(String provinceId) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE PROVINCE_ID = '" + provinceId + "'").get("PROVINCE_NAME"));
		return result;
	}
	
	public String getRoleName(String roleId) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT ROLE_NAME FROM " + this.schema + ".ACC67_ROLE WHERE ROLE_ID = '" + roleId + "'").get("ROLE_NAME"));
		return result;
	}
	
	public List lsProvince() throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT PROVINCE_ID, PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE NHSO_ZONE NOT IN ('14', '15') ORDER BY PROVINCE_NAME ASC");
		return lsData;
	}
	public List lsProvinceById(String provinceId) throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT PROVINCE_ID, PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE PROVINCE_ID = '" + provinceId + "' ORDER BY PROVINCE_NAME ASC");
		return lsData;
	}
	public List lsProvinceOrDerById() throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT PROVINCE_ID, PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE NHSO_ZONE NOT IN ('14', '15') ORDER BY PROVINCE_ID ASC");
		return lsData;
	}
	public String getProvinceAddressById(String provinceId) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT PROVINCE_ACCOUNT_ADDRESS FROM "+this.schema+".ACC6_PROVINCE_ACCOUNT WHERE PROVINCE_ID = '"+provinceId+"'").get("PROVINCE_ACCOUNT_ADDRESS"));	
		return result;
	}
	public List lsProvinceByZone(String nhsoZone) throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT PROVINCE_ID, PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE NHSO_ZONE = '" + nhsoZone + "' ORDER BY PROVINCE_NAME ASC");
		return lsData;
	}
	
	public List lsBudgetYear() throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT DISTINCT BUDGET_YEAR FROM "+this.schema+".ACC6_ALLOCATE ORDER BY BUDGET_YEAR DESC");
		return lsData;
	}
	
	public List lsBudgetYearAc() throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT DISTINCT BUDGET_YEAR-543 AS BUDGET_YEAR FROM "+this.schema+".ACC6_ALLOCATE ORDER BY BUDGET_YEAR DESC");
		return lsData;
	}
	public List lsAllocateByBudgetYear(String budgetYear) throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT ALLOCATE_CODE, ALLOCATE_NAME FROM " + this.schema + ".ACC6_ALLOCATE WHERE ALLOCATE_APPLY='Y' AND ACTIVE='Y' AND BUDGET_YEAR = '"+budgetYear+"' ORDER BY ALLOCATE_SEQ ASC ");
		return lsData;
	}
	public List lsAllocateLvl1() throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT ALLOCATE_CODE, ALLOCATE_NAME FROM " + this.schema + ".ACC6_ALLOCATE WHERE ALLOCATE_LEVEL = 1 ORDER BY ALLOCATE_SEQ ASC ");
		return lsData;
	}
	public List lsAllocateLvl1ByBudgetYear(String budgetYear) throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT ALLOCATE_CODE, ALLOCATE_NAME FROM " + this.schema + ".ACC6_ALLOCATE WHERE ALLOCATE_LEVEL = 1 AND BUDGET_YEAR = '"+budgetYear+"' ORDER BY ALLOCATE_SEQ ASC ");
		return lsData;
	}
	public List lsAllocateLvl2ByBudgetYear(String budgetYear, String parentCode) throws Exception {
		List lsData = null;
		lsData = this.dao.listDataSql("SELECT ALLOCATE_CODE, ALLOCATE_NAME FROM " + this.schema + ".ACC6_ALLOCATE WHERE ALLOCATE_LEVEL = 2 AND BUDGET_YEAR = '"+budgetYear+"' AND PARENT_CODE = '"+parentCode+"' ORDER BY ALLOCATE_SEQ ASC ");
		return lsData;
	}
	public String sumExpenseTransferAmount(String docNo) throws Exception {
		String result = "0.00";
		result = this.dao.chkData(this.dao.findDataSql("SELECT SUM(TRANSFER_AMOUNT) AS TRANSFER_AMOUNT FROM "+this.schema+".ACC6_EXPENSE_TRANSFER WHERE DOC_NO = '"+docNo+"'").get("TRANSFER_AMOUNT"));
		return result;
	}
	
	public String sumExpenseChequeAmount(String docNo) throws Exception {
		String result = "0.00";
		result = this.dao.chkData(this.dao.findDataSql("SELECT SUM(CHEQUE_AMOUNT)+SUM(TAX_VALUE) AS CHEQUE_AMOUNT FROM "+this.schema+".ACC6_EXPENSE_CHEQUE WHERE DOC_NO = '"+docNo+"'").get("CHEQUE_AMOUNT"));
		return result;
	}
	
	public String getProvinceByVender(String venderCode) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_VENDER WHERE VENDER_CODE = '" + venderCode + "'").get("PROVINCE_ID"));
		return result;
	}
	public String getAllocateById(String allocateCode) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT ALLOCATE_NAME FROM " + this.schema + ".ACC6_ALLOCATE WHERE ALLOCATE_CODE = '" + allocateCode + "'").get("ALLOCATE_NAME"));
		return result;
	}
	public String getVenderNameById(String venderCode) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT VENDER_NAME FROM " + this.schema + ".ACC6_VENDER WHERE VENDER_CODE = '" + venderCode + "'").get("VENDER_NAME"));
		return result;
	}
	public Map getMapVenderDataById(String venderCode) throws Exception {
		Map result = new HashMap();
		result = this.dao.findDataSql("SELECT VENDER_ACCOUNT,VENDER_NAME FROM " + this.schema + ".ACC6_VENDER WHERE VENDER_CODE = '" + venderCode + "' AND ACTIVE = 'Y' ");
		return result;
	}
	public String getCategoryById(String categoryId) throws Exception {
		String result = "";
		if (categoryId != null && !categoryId.equals("")) {
			result = this.dao.chkData(this.dao.findDataSql("SELECT CATEGORY_NAME FROM " + this.schema + ".ACC67_EXPENSE_CATEGORY WHERE CATEGORY_ID = '" + categoryId + "' AND CATEGORY_ACC ='6'").get("CATEGORY_NAME"));
		}
		return result;
	}
	public String getCategoryAcc7ById(String categoryId) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT CATEGORY_NAME FROM " + this.schema + ".ACC67_EXPENSE_CATEGORY WHERE CATEGORY_ID = '" + categoryId + "' AND CATEGORY_ACC ='7'").get("CATEGORY_NAME"));
		return result;
	}
	public String sumAmountByDocNo(String docNo) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT SUM(EXPENSE_AMOUNT) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC7_EXPENSE_DETAIL WHERE DOC_NO = '" + docNo + "' AND ACTIVE ='Y'").get("EXPENSE_AMOUNT"));
		return result;
	}
	public String sumAmountByDocNo(String docNo, String catId) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC7_EXPENSE_DETAIL WHERE DOC_NO = '" + docNo + "' AND CATEGORY_ID = '"+catId+"' AND  ACTIVE ='Y'").get("EXPENSE_AMOUNT"));
		return result;
	}
	public String getProvinceIdByAccountDaily(String provinceAccountDaily) throws Exception {
		String result = "";
		result = this.dao.chkData(this.dao.findDataSql("SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_PROVINCE_ACCOUNT WHERE PROVINCE_ACCOUNT_DAILY = '" + provinceAccountDaily + "' AND  ACTIVE ='Y'").get("PROVINCE_ID"));
		return result;
	}
	public String getProvinceIdByAccountSaving(String provinceAccountSaving, String provinceAcc) throws Exception {
		String result = "";
		if (provinceAcc.equals("6")) {
			result = this.dao.chkData(this.dao.findDataSql("SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_PROVINCE_ACCOUNT WHERE PROVINCE_ACCOUNT_SAVING = '" + provinceAccountSaving + "' AND ACTIVE ='Y'").get("PROVINCE_ID"));
		} else {
			result = this.dao.chkData(this.dao.findDataSql("SELECT PROVINCE_ID FROM " + this.schema + ".ACC7_PROVINCE_ACCOUNT WHERE PROVINCE_ACCOUNT = '" + provinceAccountSaving + "' AND ACTIVE ='Y'").get("PROVINCE_ID"));
		}
		return result;
	}
	
	public String yearToYYYY(String year) throws Exception {
		
		int  y = Integer.parseInt(year) - 543;
		return String.valueOf(y);
		
	}
	
}